﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
namespace SZSLSystem.DAL
{
	/// <summary>
	///数据访问类 BankLoan (create by HC 2013/1/14 22:28:41)
	/// </summary>
	public class BankLoan
	{
		public BankLoan(){}
		public bool Exists(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(OID) from BankLoan");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.Exists(strSql.ToString(), parameters);
		}

		public int Delete(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from BankLoan");
			strSql.Append(" where OID=@OID  ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public int Add(SZSLSystem.Model.BankLoan model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into BankLoan( ");
			strSql.Append("[OID],[BuyerOID],[RoomOID],[AgreementOID],[BankName],[BankAddress],[BankUserName],[BankAccounts],[BankContacts],[BankContactsMobile],[BankContactsTelphone],[TotalPrice],[LoanMoney],[PaymentMoney],[LoanYears],[MoneyRate],[MonthlyPayment],[AddTime])");
			strSql.Append("values (@OID,@BuyerOID,@RoomOID,@AgreementOID,@BankName,@BankAddress,@BankUserName,@BankAccounts,@BankContacts,@BankContactsMobile,@BankContactsTelphone,@TotalPrice,@LoanMoney,@PaymentMoney,@LoanYears,@MoneyRate,@MonthlyPayment,@AddTime)");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyerOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomOID",SqlDbType.VarChar,50),
					new SqlParameter ("@AgreementOID",SqlDbType.VarChar,50),
					new SqlParameter ("@BankName",SqlDbType.VarChar,50),
					new SqlParameter ("@BankAddress",SqlDbType.VarChar,50),
					new SqlParameter ("@BankUserName",SqlDbType.VarChar,50),
					new SqlParameter ("@BankAccounts",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContacts",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContactsMobile",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContactsTelphone",SqlDbType.VarChar,50),
					new SqlParameter ("@TotalPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@LoanMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@PaymentMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@LoanYears",SqlDbType.Int,4),
					new SqlParameter ("@MoneyRate",SqlDbType.VarChar,50),
					new SqlParameter ("@MonthlyPayment",SqlDbType.VarChar,50),
					new SqlParameter ("@AddTime",SqlDbType.DateTime)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.BuyerOID;
			parameters[2].Value = model.RoomOID;
			parameters[3].Value = model.AgreementOID;
			parameters[4].Value = model.BankName;
			parameters[5].Value = model.BankAddress;
			parameters[6].Value = model.BankUserName;
			parameters[7].Value = model.BankAccounts;
			parameters[8].Value = model.BankContacts;
			parameters[9].Value = model.BankContactsMobile;
			parameters[10].Value = model.BankContactsTelphone;
			parameters[11].Value = model.TotalPrice;
			parameters[12].Value = model.LoanMoney;
			parameters[13].Value = model.PaymentMoney;
			parameters[14].Value = model.LoanYears;
			parameters[15].Value = model.MoneyRate;
			parameters[16].Value = model.MonthlyPayment;
			parameters[17].Value = model.AddTime;
			object obj = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
			if (obj == null)
			{
				return 1;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}

		public  SZSLSystem.Model.BankLoan GetModel(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select top 1 [OID],[BuyerOID],[RoomOID],[AgreementOID],[BankName],[BankAddress],[BankUserName],[BankAccounts],[BankContacts],[BankContactsMobile],[BankContactsTelphone],[TotalPrice],[LoanMoney],[PaymentMoney],[LoanYears],[MoneyRate],[MonthlyPayment],[AddTime] from BankLoan");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			SZSLSystem.Model.BankLoan model=new SZSLSystem.Model.BankLoan();
			DataSet ds=DbHelperOleDb.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				DataRow row = ds.Tables[0].Rows[0];
						model.OID=row["OID"].ToString();
						model.BuyerOID=row["BuyerOID"].ToString();
						model.RoomOID=row["RoomOID"].ToString();
						model.AgreementOID=row["AgreementOID"].ToString();
						model.BankName=row["BankName"].ToString();
						model.BankAddress=row["BankAddress"].ToString();
						model.BankUserName=row["BankUserName"].ToString();
						model.BankAccounts=row["BankAccounts"].ToString();
						model.BankContacts=row["BankContacts"].ToString();
						model.BankContactsMobile=row["BankContactsMobile"].ToString();
						model.BankContactsTelphone=row["BankContactsTelphone"].ToString();
						model.TotalPrice=row["TotalPrice"].ToString();
						model.LoanMoney=row["LoanMoney"].ToString();
						model.PaymentMoney=row["PaymentMoney"].ToString();
					if (row["LoanYears"].ToString()!="")
					{
						model.LoanYears=int.Parse(row["LoanYears"].ToString());
					}
						model.MoneyRate=row["MoneyRate"].ToString();
						model.MonthlyPayment=row["MonthlyPayment"].ToString();
					if (row["AddTime"].ToString()!="")
					{
						model.AddTime=DateTime.Parse(row["AddTime"].ToString());
					}

				return model;
			}
			else
			{
				return null;
			}
		}

		public int Update(SZSLSystem.Model.BankLoan model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update BankLoan set ");
			strSql.Append("[OID]=@OID,");
			strSql.Append("[BuyerOID]=@BuyerOID,");
			strSql.Append("[RoomOID]=@RoomOID,");
			strSql.Append("[AgreementOID]=@AgreementOID,");
			strSql.Append("[BankName]=@BankName,");
			strSql.Append("[BankAddress]=@BankAddress,");
			strSql.Append("[BankUserName]=@BankUserName,");
			strSql.Append("[BankAccounts]=@BankAccounts,");
			strSql.Append("[BankContacts]=@BankContacts,");
			strSql.Append("[BankContactsMobile]=@BankContactsMobile,");
			strSql.Append("[BankContactsTelphone]=@BankContactsTelphone,");
			strSql.Append("[TotalPrice]=@TotalPrice,");
			strSql.Append("[LoanMoney]=@LoanMoney,");
			strSql.Append("[PaymentMoney]=@PaymentMoney,");
			strSql.Append("[LoanYears]=@LoanYears,");
			strSql.Append("[MoneyRate]=@MoneyRate,");
			strSql.Append("[MonthlyPayment]=@MonthlyPayment,");
			strSql.Append("[AddTime]=@AddTime");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyerOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomOID",SqlDbType.VarChar,50),
					new SqlParameter ("@AgreementOID",SqlDbType.VarChar,50),
					new SqlParameter ("@BankName",SqlDbType.VarChar,50),
					new SqlParameter ("@BankAddress",SqlDbType.VarChar,50),
					new SqlParameter ("@BankUserName",SqlDbType.VarChar,50),
					new SqlParameter ("@BankAccounts",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContacts",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContactsMobile",SqlDbType.VarChar,50),
					new SqlParameter ("@BankContactsTelphone",SqlDbType.VarChar,50),
					new SqlParameter ("@TotalPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@LoanMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@PaymentMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@LoanYears",SqlDbType.Int,4),
					new SqlParameter ("@MoneyRate",SqlDbType.VarChar,50),
					new SqlParameter ("@MonthlyPayment",SqlDbType.VarChar,50),
					new SqlParameter ("@AddTime",SqlDbType.DateTime)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.BuyerOID;
			parameters[2].Value = model.RoomOID;
			parameters[3].Value = model.AgreementOID;
			parameters[4].Value = model.BankName;
			parameters[5].Value = model.BankAddress;
			parameters[6].Value = model.BankUserName;
			parameters[7].Value = model.BankAccounts;
			parameters[8].Value = model.BankContacts;
			parameters[9].Value = model.BankContactsMobile;
			parameters[10].Value = model.BankContactsTelphone;
			parameters[11].Value = model.TotalPrice;
			parameters[12].Value = model.LoanMoney;
			parameters[13].Value = model.PaymentMoney;
			parameters[14].Value = model.LoanYears;
			parameters[15].Value = model.MoneyRate;
			parameters[16].Value = model.MonthlyPayment;
			parameters[17].Value = model.AddTime;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select [OID],[BuyerOID],[RoomOID],[AgreementOID],[BankName],[BankAddress],[BankUserName],[BankAccounts],[BankContacts],[BankContactsMobile],[BankContactsTelphone],[TotalPrice],[LoanMoney],[PaymentMoney],[LoanYears],[MoneyRate],[MonthlyPayment],[AddTime]");
			strSql.Append(" FROM BankLoan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(int top,string strWhere,string fieldOrder)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select");
			if(top>0)
			{
				strSql.Append(" top "+top.ToString());
			}
			strSql.Append(" [OID],[BuyerOID],[RoomOID],[AgreementOID],[BankName],[BankAddress],[BankUserName],[BankAccounts],[BankContacts],[BankContactsMobile],[BankContactsTelphone],[TotalPrice],[LoanMoney],[PaymentMoney],[LoanYears],[MoneyRate],[MonthlyPayment],[AddTime]");
			strSql.Append(" FROM BankLoan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
				strSql.Append(" order by "+fieldOrder);
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(string showFields,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+showFields);
			strSql.Append(" FROM BankLoan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public object GetSingle(string fieldName,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+fieldName);
			strSql.Append(" FROM BankLoan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.GetSingle(strSql.ToString());
		}

	}
}